#!/usr/bin/env python
# -*- encoding: utf-8 -*-
# Project: spd-sxmcc
"""
@author: lyndon
@time Created on 2018/11/22 13:11
@desc
"""

import json
import MySQLdb
from com.teradata.mysqldemo.errors import TypeMappingError

class MySQLOpr:
    def __init__(self, db_infos):
        self.myconn = MySQLdb.connect(db=db_infos.get('database'), user=db_infos.get('username'),
                                      passwd=db_infos.get('password'), host=db_infos.get('host'),
                                      port=int(db_infos.get('port')), charset='utf8mb4')
        self.mycursor = self.myconn.cursor()

    def __del__(self):
        self.mycursor.close()
        self.myconn.close()

    def commit(self):
        self.myconn.commit()

    def close(self):
        self.mycursor.close()
        self.myconn.close()

    def rollback(self):
        self.myconn.rollback()

    def select_star_all(self, table_name):
        sql = ("select * from %s" % (table_name))
        try:
            self.mycursor.execute(sql)
            result = self.mycursor.fetchone()
        except Exception as e:
            print('select Exception', e)
        else:
            return result


    def select(self, table_name, column_names):
        colnames = ','.join(column_names)
        sql = ("select %s from %s" % (colnames, table_name))
        try:
            self.mycursor.execute(sql)
            result = self.mycursor.fetchall()
        except Exception as e:
            print('select Exception', e)
        else:
            return result

    def select_sql(self, sql):
        try:
            self.mycursor.execute(sql)
            result = self.mycursor.fetchall()
        except Exception as e:
            print('select Exception', e)
        else:
            return result

    def exec_sql(self, sql):
        try:
            self.mycursor.execute(sql)
            result = self.mycursor.fetchall()
            self.commit()  # 这个对于增删改是必须的，否则事务没提交执行不成功
            self.close()
            return result
        except MySQLdb.Error as e:
            print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

    def getddl(self, schema_name, table_name):
        sql = ("select column_name, data_type, character_maximum_length, numeric_precision, numeric_scale" \
               " from information_schema.columns t1" \
               " where table_schema= '%s' " \
               " and table_name='%s'" \
               " order by ordinal_position" % (schema_name, table_name))
        print(sql)
        try:
            self.mycursor.execute(sql)
            result = self.mycursor.fetchall()
        except Exception as e:
            print('getddl Exception', e)
        else:
            return result

    def getSynTask(self):
        """
        获取同步任务
        :return:
        """
        sql = ("select t1.job_id,t1.job_type,t1.job_detail,t1.user_name,t1.table_name,t1.status,t1.rate,t2.resultdb" \
               " from hive_job t1" \
               " inner join spd_user t2" \
               " on t1.user_name=t2.username")
        # pgcursor = self.pgconn.cursor()
        try:
            self.mycursor.execute(sql)
            result = self.mycursor.fetchall()
        except Exception as e:
            print('getSynTask Exception', e)
        else:
            return result


    @classmethod
    def getconninfo(cls, login_str):
        db_infos = {}
        dbtype, dbinfo = login_str.split("://")
        loginInfo, hostInfo = dbinfo.split('@')
        userName, passWord = loginInfo.split(':')
        hostInfo, dataBase = hostInfo.split('/')
        host, port = hostInfo.split(':')
        db_infos.setdefault('dbtype', dbtype)
        db_infos.setdefault('username', userName)
        db_infos.setdefault('password', passWord)
        db_infos.setdefault('host', host)
        db_infos.setdefault('port', port)
        db_infos.setdefault('database', dataBase)
        return db_infos

    @classmethod
    def get_source_infos(cls, dbinfo):
        'jdbc:mysql://192.168.100.161:3306/result_test_db?characterEncoding=utf8'
        protocol = 'jdbc:mysql://'
        url = protocol + dbinfo.get('host', '') + ':' + dbinfo.get('port', '') + '/' + dbinfo.get('database', '') + '?characterEncoding=utf8'
        username = dbinfo.get('username', '')
        password = dbinfo.get('password', '')
        return url, username, password


if __name__ == "__main__":


    # conn_info = 'sqlalchemy+mysql+resultdb://root:newpasswd@192.168.100.162:3306/resultdb'    #
    conn_info = 'sqlalchemy+mysql+resultdb://result_test:result_test_1Q#@192.168.100.161:3306/result_test_db'  #
    dbinfo = MySQLOpr.getconninfo(conn_info)
    sdb = MySQLOpr(dbinfo)
    # table_name = 'fangtianxia_rsf_ty'
    table_name = 'fangtianxia_ty_rsf'
    with open(table_name + '.txt', 'w') as f:
        columns = ['taskid', 'url', 'result', 'updatetime']
        res = sdb.select(table_name, columns)
        try:
            num = 0
            for xx in res:
                taskid = xx[0]
                url = xx[1]
                result = xx[2]
                updatetime = xx[3]

                # f.write(taskid.encode('utf-8') + '\t' + url.encode('utf-8') + '\t' + str(updatetime) + '\t')
                dic = dict(json.loads(result))
                #
                name = dic.get('name').encode('utf-8')
                name = name.replace('小区网', '小区')
                print(str(num) + ": --------- " + name + " -----------")

                f.write(name + '\t' + url.encode('utf-8') + '\t')

                x = [i.encode('utf-8').replace(' ', '').replace('\n', '').replace('\t', '') for i in dic.get('price')]

                try:
                    price = x[0]
                except Exception as e:
                    price = '0'
                try:
                    rr_month = x[1]
                except Exception as e:
                    rr_month = '0'
                try:
                    rr_year = x[2]
                except Exception as e:
                    rr_year = '0'

                f.write(price + '\t' + rr_month + '\t' + rr_year + '\t')

                # 输出基本信息
                infos = {}
                x = (i.encode('utf-8').replace(' ', '').replace('\n', '').replace('\t', '').replace(' ', '') for i in dic.get('infos') if "：" in i.encode('utf-8'))
                [infos.setdefault(i.split('：')[0], i.split('：')[1]) for i in x]
                # for i in infos:
                #     print(i + ' -> ' + infos[i])
                f.write(infos.get('所属区域', '') + '\t' + infos.get('楼栋总数', '') + '\t' + infos.get('房屋总数', '') + '\t' + infos.get('容积率', '') + '\t' + infos.get('占地面积', '') + '\t' + infos.get('建筑面积', '') + '\t' + infos.get('建筑类型', '') + '\t' + infos.get('建筑结构', '') + '\t' + infos.get('建筑年代', '') + '\t' + infos.get('小区入口', '')+ '\t' + infos.get('小区地址', '') + '\t' + infos.get('开发商', '') + '\t' + infos.get('产权描述', '') + '\t' + infos.get('售楼地址', '') + '\t' + infos.get('邮编', '') + '\t' + infos.get('物业类别', '') + '\t' + infos.get('物业费', '') + '\t' + infos.get('通讯设备', '')  + '\t' + infos.get('绿化率', '') + '\t' + infos.get('燃气', '') + '\t' + infos.get('供水', '') + '\t' + infos.get('供电', '') + '\t' + infos.get('供暖', '') + '\t' + infos.get('卫生服务', '') + '\t' + infos.get('附加信息', '')  + '\t')

                # 输出其他信息
                others = {}
                x = (i.encode('utf-8').replace(' ', '').replace('\n', '').replace('\t', '') for i in dic.get('others') if "：" in i.encode('utf-8'))
                [others.setdefault(i.split('：')[0], i.split('：')[1]) for i in x]
                # for i in others:
                #     print(i + ' -> ' + others[i])
                f.write(others.get('电梯服务', '') + '\t' + others.get('安全管理', '') + '\t' + others.get('停车位', '') + '\t' + others.get('公墓/殡仪馆', '') + '\t')

                # 交通信息
                traffics = {}
                x = (i.encode('utf-8').replace(' ', '').replace('\n', '').replace('\t', '').replace(' ', '') for i in dic.get('traffics') if "：" in i.encode('utf-8'))
                [traffics.setdefault(i.split('：')[0], i.split('：')[1]) for i in x]
                # for i in traffics:
                #     print(i + ' -> ' + traffics[i])
                f.write(traffics.get('公交', '') + '\t' + traffics.get('医院', '') + '\t' + traffics.get('商场', '') + '\t' + traffics.get('银行', '') + '\t' + traffics.get('幼儿园', '') + '\t' + traffics.get('大学', '') + '\t' + traffics.get('中小学', '') + '\t' + traffics.get('邮局', '') + '\t' + traffics.get('其他', ''))
                f.write('\n')
                num += 1
        except Exception as e:
            print('Exception:', e)
            sdb.rollback()
        else:
            sdb.commit()
            # sdb.close()
